﻿using PARK.Common.Enum;
using PARK.Common.Extension;
using PARK.Common.Filter;
using PARK.Dependency;
using PARK.DynamicApiController;
using PARK.FriendlyException;
using Mapster;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Yitter.IdGenerator;
using PARK.Common.Helper;
using PARK.JsonSerialization;
using PARK.Welfare.Interfaces.WelfareBill;
using PARK.Welfare.Entitys;
using PARK.Common.Core.Manager;
using PARK.Welfare.Entitys.Dto.WelfareBill;
using PARK.ClayObject;
using PARK.Common.Model.NPOI;
using PARK.Common.Configuration;
using PARK.DataEncryption;
using PARK.Welfare.Interfaces.WelfareDetail;
using PARK.Welfare.Entitys.Dto.WelfareDetail;
using System.Text;

namespace PARK.Welfare.WelfareDetail
{
    /// <summary>
    /// 发放明细服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "Welfare", Name = "Details", Order = 200)]
    [Route("api/welfare/[controller]")]
    public class WelfareDetailsService : IWelfareDetailService, IDynamicApiController, ITransient
    {
        private readonly ISqlSugarRepository<WelfareDetailEntity> _welfareDetailRepository;
        private readonly SqlSugarScope _db;
        private readonly IUserManager _userManager;

        /// <summary>
        /// 初始化一个<see cref="WelfareBillService"/>类型的新实例
        /// </summary>
        public WelfareDetailsService(
            ISqlSugarRepository<WelfareDetailEntity> welfareDetaRepository,
            IUserManager userManager)
        {
            _welfareDetailRepository = welfareDetaRepository;
            _db = _welfareDetailRepository.Context;
            _userManager = userManager;
        }

        /// <summary>
        /// 获取福利发放明细
        /// </summary>
        /// <param name="id">参数</param>
        /// <returns></returns>
        [HttpGet("{id}")]
        public async Task<dynamic> GetInfo(string id)
        {
            var entity = await _db.Queryable<WelfareDetailEntity>().FirstAsync(p => p.BillId == id);
            var output = entity.Adapt<WelfareDetailInfoOutput>();
            return output;
        }
        /// <summary>
        /// 获取发放明细无分页列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("GetAllListByEnddate")]
        public async Task<dynamic> GetAllListByEnddate([FromQuery] WelfareDetailListQueryInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            var sidx = input.sidx == null ? "jobNo" : input.sidx;
            DateTime enddate = Convert.ToDateTime(input.endDate).AddDays(1);
            DateTime dt = DateTime.Parse(Convert.ToDateTime(enddate).ToString("yyyy-MM-dd") + " 00:00:00");
            //获得本月月初时间
            var startMonth = dt.AddDays(1 - dt.Day);
            //获得本月月末时间
            DateTime s = DateTime.Parse(Convert.ToDateTime(enddate).ToString("yyyy-MM-dd") + " 23:59:59");
            DateTime ss = s.AddDays(1 - s.Day);
            var endMonth = ss.AddMonths(1).AddDays(-1);

            var clearlist = await _db.Queryable<WelfareClearEntity>().Where(t => t.CompanyId.Equals(userInfo.companyId)).ToListAsync();
            foreach (var item in clearlist)
            {
                if (item.ClearDate>= endMonth)
                {
                    throw PARKException.Oh("该清零日期之前已经清零,无需再次清零！");
                }
            }
            //发放日期
            //List<string> queryBillDate = input.inputBillDate != null ? input.inputBillDate.Split(',').ToObeject<List<string>>() : null;
            //DateTime? startBilldate = queryBillDate != null ? Ext.GetDateTime(queryBillDate.First()) : null;
            //DateTime? endBilldate = queryBillDate != null ? Ext.GetDateTime(queryBillDate.Last()) : null;

            StringBuilder dbSql = new StringBuilder("select b.EmployeeId,b.Amount,a.WelfareBalance ConSumAmt,(a.WelfareBalance-b.Amount) as Balance,a.RealName reallName,a.JobNum jobNo,a.DepartId,a.DepartName,a.CardNo,a.CatCardNo,'' summary from base_employee a right join ( ");

            dbSql.AppendFormat(" select EmployeeId,sum(Amount) Amount from welfare_detail where BegDate>'{0}' and IsAccounted=1 and Balance>0 and CompanyId='{1}'", new DateTime(endMonth.ToDate().Year, endMonth.ToDate().Month, endMonth.ToDate().Day, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss"), userInfo.companyId);
            dbSql.AppendFormat(" group by employeeId) b on a.EmployeeId=b.EmployeeId where a.RealName is not null and a.F_DeleteMark=0 and a.CompanyId='{0}' and a.AccStatus!=4 and a.WelfareBalance>b.Amount", userInfo.companyId);

            var data = _db.SqlQueryable<WelfareDetailListOutput>(dbSql.ToString()).Select(it => new WelfareDetailListOutput
            {
                employeeId = it.employeeId,
                jobNo = it.jobNo,
                reallName = it.reallName,
                departId = it.departId,
                departName = it.departName,
                cardNo = it.cardNo,
                catCardNo = it.catCardNo,
                amount = it.amount,
                summary = it.summary,
                conSumAmt = it.conSumAmt,
                balance = it.balance,
            }).OrderBy(sidx + " asc ").ToList();
            //var data = await _db.Queryable<WelfareDetailEntity>()
            //     .Where(p => p.CompanyId.Equals(userInfo.companyId))//权限
            //    //关键字查询
            //    //.WhereIF(!input.keyword.IsNullOrEmpty(), p => p.JobNo.Contains(input.keyword) || p.ReallName.Contains(input.keyword) || p.CardNo.Equals(input.keyword))
            //    //发放日期
            //    //.WhereIF(queryBillDate != null, a => a.BillDate >= new DateTime(startBilldate.ToDate().Year, startBilldate.ToDate().Month, startBilldate.ToDate().Day, 0, 0, 0))
            //    //.WhereIF(queryBillDate != null, a => a.BillDate <= new DateTime(endBilldate.ToDate().Year, endBilldate.ToDate().Month, endBilldate.ToDate().Day, 23, 59, 59))
            //    .Where(a => a.IsAccounted == 1 && a.Balance > 0)
            //    .WhereIF(input.endDate != null, a => a.EndDate <= new DateTime(input.endDate.ToDate().Year, input.endDate.ToDate().Month, input.endDate.ToDate().Day, 23, 59, 59))
            //.Select(it => new WelfareDetailListOutput
            //{
            //    detailsid = it.Detailsid,
            //     billId =it.BillId,
            //    employeeId = it.EmployeeId,
            //    jobNo = it.JobNo,
            //    reallName = it.ReallName,
            //    departId = it.DepartId,
            //    departName = it.DepartName,
            //    cardNo = it.CardNo,
            //    amount = it.Amount,
            //    summary = it.Summary,
            //    begDate = Convert.ToDateTime(it.BegDate).ToString("yyyy-MM-dd"),
            //    endDate = Convert.ToDateTime(it.EndDate).ToString("yyyy-MM-dd"),
            //    conSumAmt = it.ConSumAmt,
            //    balance = it.Balance,
            //}).MergeTable().OrderBy(sidx + " asc ").ToListAsync();
            return data;
        }
        /// <summary>
		/// 获取福利发放明细列表
		/// </summary>
		/// <param name="input">请求参数</param>
		/// <returns></returns>
        [HttpGet("")]
        public async Task<dynamic> GetList([FromQuery] WelfareDetailListQueryInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            var sidx = input.sidx == null ? "detailsid" : input.sidx;
            //选中公司查询全部
            if (input.departId == userInfo.companyId)
            {
                input.departId = null;
            }
            //发放日期
            List<string> queryBillDate = input.inputBillDate != null ? input.inputBillDate.Split(',').ToObeject<List<string>>() : null;
            DateTime? startBilldate = queryBillDate != null ? Ext.GetDateTime(queryBillDate.First()) : null;
            DateTime? endBilldate = queryBillDate != null ? Ext.GetDateTime(queryBillDate.Last()) : null;
            //所属时段
            List<string> querybegendDate = input.begendDate != null ? input.begendDate.Split(',').ToObeject<List<string>>() : null;
            DateTime? startbegenddate = querybegendDate != null ? Ext.GetDateTime(querybegendDate.First()) : null;
            DateTime? endbegendDatedate = querybegendDate != null ? Ext.GetDateTime(querybegendDate.Last()) : null;
            var data = await _db.Queryable<WelfareDetailEntity>()
                 .Where(p => p.CompanyId.Equals(userInfo.companyId))//权限
                 .WhereIF(!string.IsNullOrEmpty(input.departId), p => p.DepartId.Equals(input.departId))
                 .Where(a => a.IsAccounted == 1)
                //关键字查询
                .WhereIF(!input.keyword.IsNullOrEmpty(), p => p.JobNo.Contains(input.keyword) || p.ReallName.Contains(input.keyword) || p.CardNo.Equals(input.keyword))
                //发放日期
                .WhereIF(queryBillDate != null, a => a.BillDate >= new DateTime(startBilldate.ToDate().Year, startBilldate.ToDate().Month, startBilldate.ToDate().Day, 0, 0, 0))
                .WhereIF(queryBillDate != null, a => a.BillDate <= new DateTime(endBilldate.ToDate().Year, endBilldate.ToDate().Month, endBilldate.ToDate().Day, 23, 59, 59))
                //所属时段
                .WhereIF(querybegendDate != null, a => a.BegDate >= new DateTime(startbegenddate.ToDate().Year, startbegenddate.ToDate().Month, startbegenddate.ToDate().Day, 0, 0, 0))
                .WhereIF(querybegendDate != null, a => a.EndDate <= new DateTime(endbegendDatedate.ToDate().Year, endbegendDatedate.ToDate().Month, endbegendDatedate.ToDate().Day, 23, 59, 59))
                .Select(it => new WelfareDetailListOutput
                {
                    detailsid = it.Detailsid,
                    billId = it.BillId,
                    employeeId = it.EmployeeId,
                    jobNo = it.JobNo,
                    reallName = it.ReallName,
                    departId = it.DepartId,
                    departName = it.DepartName,
                    cardNo = it.CardNo,
                    amount = it.Amount,
                    summary = it.Summary,
                    isAccounted = it.IsAccounted,
                    accountedDate = Convert.ToDateTime(it.AccountedDate).ToString("yyyy-MM-dd"),
                    billDate = Convert.ToDateTime(it.BillDate).ToString("yyyy-MM-dd"),
                    billMonth= Convert.ToDateTime(it.BegDate).ToString("yyyy-MM"),
                    begDate = Convert.ToDateTime(it.BegDate).ToString("yyyy-MM-dd"),
                    endDate = Convert.ToDateTime(it.EndDate).ToString("yyyy-MM-dd"),
                    conSumAmt = it.ConSumAmt,
                    balance = it.Balance,
                }).MergeTable().OrderBy(sidx + " " + input.sort).ToPagedListAsync(input.currentPage, input.pageSize);
            return PageResult<WelfareDetailListOutput>.SqlSugarPageResult(data);
        }

        /// <summary>
		/// 获取福利发放使用情况
		/// </summary>
		/// <param name="input">请求参数</param>
		/// <returns></returns>
        [HttpGet("GetWelfareUseList")]
        public async Task<dynamic> GetWelfareUseList([FromQuery] WelfareDetailListQueryInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            var sidx = input.sidx == null ? "departName" : input.sidx;
            if (input.departId == userInfo.companyId)
            {
                input.departId = null;
            }
            //发放日期
            List<string> queryBillDate = input.inputBillDate != null ? input.inputBillDate.Split(',').ToObeject<List<string>>() : null;
            DateTime? startBilldate = queryBillDate != null ? Ext.GetDateTime(queryBillDate.First()) : null;
            DateTime? endBilldate = queryBillDate != null ? Ext.GetDateTime(queryBillDate.Last()) : null;
            if (userInfo != null)
            {
                StringBuilder dbSql = new StringBuilder("select departid,departname,sum(Amount) Amount,sum(ConSumAmt) ConSumAmt,sum(Balance) Balance from welfare_detail  where ISNULL(IsAccounted,0)=1 ");
                if (!string.IsNullOrEmpty(input.keyword))
                {
                    dbSql.AppendFormat(" and (JobNo like '%{0}%' or ReallName like '%{0}%' or CardNo like '%{0}%' or BillCode like '%{0}%')", input.keyword);
                }
                if (!string.IsNullOrEmpty(input.departId))
                {
                    dbSql.AppendFormat(" and departid= '{0}'", input.departId);
                }
                if (input.inputBillDate != null)
                {
                    dbSql.AppendFormat(" and BillDate>='{0}'", new DateTime(startBilldate.ToDate().Year, startBilldate.ToDate().Month, startBilldate.ToDate().Day, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss"));
                }
                if (input.inputBillDate != null)
                {
                    dbSql.AppendFormat(" and BillDate<='{0}'", new DateTime(endBilldate.ToDate().Year, endBilldate.ToDate().Month, endBilldate.ToDate().Day, 23, 59, 59).ToString("yyyy-MM-dd HH:mm:ss"));
                }
                dbSql.AppendFormat(" and CompanyId='{0}'", userInfo.companyId);
                dbSql.AppendFormat(" group by departid,departname ");
                var data = _db.SqlQueryable<WelfareDetailListOutput>(dbSql.ToString()).Select(it => new WelfareDetailListOutput
                {
                    departId = it.departId,
                    departName = it.departName,
                    amount = it.amount,
                    conSumAmt = it.conSumAmt,
                    balance = it.balance
                }).ToList();
                return data;
            }
            else
            {
                return null;
            }
        }

        #region 福利发放
        /// <summary>
        /// 福利发放
        /// </summary>
        /// <param name="input">参数</param>
        /// <returns></returns>
        [HttpPost("")]
        public async Task Create([FromBody] WelfareDetailCrInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            try
            {
                var entity = input.Adapt<WelfareBillEntity>();
                //开启事务
                _db.BeginTran();
                //发放日期及时段
                entity.BillDate = Convert.ToDateTime(entity.BillDate);
                List<string> queryBillDate = input.inputTime != null ? input.inputTime.Split(',').ToObeject<List<string>>() : null;
                DateTime? startBilldate = queryBillDate != null ? Ext.GetDateTime(queryBillDate.First()) : null;
                DateTime? endBilldate = queryBillDate != null ? Ext.GetDateTime(queryBillDate.Last()) : null;
                entity.BegDate = startBilldate;
                entity.EndDate = endBilldate;
                entity.CompanyId = userInfo.companyId;
                entity.CompanyName = userInfo.companyName;
                //新增福利发放记录
                await _db.Insertable(entity).IgnoreColumns(ignoreNullColumn: true).ExecuteReturnEntityAsync();


                //关闭事务
                _db.CommitTran();
            }
            catch (Exception ex)
            {
                //回滚事务
                _db.RollbackTran();
                throw PARKException.Oh(ErrorCode.COM1000);
            }
            //var userInfo = await _userManager.GetUserInfo();
            //var  entity = input.Adapt<WelfareBillEntity>();
            //entity.billId = YitIdHelper.NextId().ToString();
            //var isOk = await _db.Insertable(entity).IgnoreColumns(ignoreNullColumn: true).ExecuteCommandAsync();
            //if (!(isOk > 0)) throw JNPFException.Oh(ErrorCode.COM1000);
        }
        #endregion

        #region 导出Excel
        /// <summary>
        /// 获取发放明细无分页列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [NonAction]
        public async Task<dynamic> GetNoPagingList([FromQuery] WelfareDetailListQueryInput input)
        {
            var sidx = input.sidx == null ? "detailsid" : input.sidx;
            var userInfo = await _userManager.GetUserInfo();
            //发放日期
            List<string> queryBillDate = input.inputBillDate != null ? input.inputBillDate.Split(',').ToObeject<List<string>>() : null;
            DateTime? startBilldate = queryBillDate != null ? Ext.GetDateTime(queryBillDate.First()) : null;
            DateTime? endBilldate = queryBillDate != null ? Ext.GetDateTime(queryBillDate.Last()) : null;
            var data = await _db.Queryable<WelfareDetailEntity>()
                 .Where(p => p.CompanyId.Equals(userInfo.companyId))//权限
                                                                    //关键字查询
                .WhereIF(!input.keyword.IsNullOrEmpty(), p => p.JobNo.Contains(input.keyword) || p.ReallName.Contains(input.keyword) || p.CardNo.Equals(input.keyword))
                //发放日期
                .WhereIF(queryBillDate != null, a => a.BillDate >= new DateTime(startBilldate.ToDate().Year, startBilldate.ToDate().Month, startBilldate.ToDate().Day, 0, 0, 0))
                .WhereIF(queryBillDate != null, a => a.BillDate <= new DateTime(endBilldate.ToDate().Year, endBilldate.ToDate().Month, endBilldate.ToDate().Day, 23, 59, 59))
                .Select(it => new WelfareDetailListOutput
                {
                    detailsid = it.Detailsid,
                    billId = it.BillId,
                    employeeId = it.EmployeeId,
                    jobNo = it.JobNo,
                    reallName = it.ReallName,
                    departId = it.DepartId,
                    departName = it.DepartName,
                    cardNo = it.CardNo,
                    amount = it.Amount,
                    summary = it.Summary,
                    isAccounted = it.IsAccounted,
                    accountedDate = Convert.ToDateTime(it.AccountedDate).ToString("yyyy-MM-dd"),
                    billDate = Convert.ToDateTime(it.BillDate).ToString("yyyy-MM-dd"),
                    begDate = Convert.ToDateTime(it.BegDate).ToString("HH:mm:ss"),
                    endDate = Convert.ToDateTime(it.EndDate).ToString("HH:mm:ss"),
                    conSumAmt = it.ConSumAmt,
                    balance = it.Balance,
                }).MergeTable().OrderBy(sidx + " " + input.sort).ToListAsync();
            return data;
        }

        /// <summary>
        /// 导出发放明细
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("Actions/Export")]
        public async Task<dynamic> Export([FromQuery] WelfareDetailListQueryInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            var exportData = new List<WelfareDetailListOutput>();
            var exportDataNew = new List<WelfareDetailListOutput>();

            var entity = input.Adapt<WelfareDetailEntity>();

            if (input.dataType == 0)
            {
                var data = Clay.Object(await this.GetList(input));
                exportData = data.Solidify<PageResult<WelfareDetailListOutput>>().list;
            }
            else
            {
                exportData = await this.GetNoPagingList(input);
            }

            foreach (WelfareDetailListOutput item in exportData)
            {
                exportDataNew.Add(item);
            }

            List<ParamsModel> paramList = "[{\"value\":\"发放单号\",\"field\":\"billCode\"},{\"value\":\"发放日期\",\"field\":\"billDate\"},{\"value\":\"所属时段\",\"field\":\"begDate\"},{\"value\":\"工号\",\"field\":\"jobNo\"},{\"value\":\"姓名\",\"field\":\"reallName\"},{\"value\":\"部门\",\"field\":\"departName\"},{\"value\":\"发放金额\",\"field\":\"amount\"},{\"value\":\"已消费额\",\"field\":\"conSumAmt\"},{\"value\":\"余额\",\"field\":\"balance\"},{\"value\":\"摘要\",\"field\":\"summary\"},]".ToList<ParamsModel>();
            ExcelConfig excelconfig = new ExcelConfig();
            excelconfig.FileName = userInfo.organizeName + "_" + "福利发放明细.xls";
            excelconfig.HeadFont = "微软雅黑";
            excelconfig.HeadPoint = 10;
            excelconfig.IsAllSizeColumn = true;
            excelconfig.ColumnModel = new List<ExcelColumnModel>();
            List<string> selectKeyList = input.selectKey.Split(',').ToList();
            foreach (var item in selectKeyList)
            {
                var isExist = paramList.Find(p => p.field == item);
                if (isExist != null)
                {
                    excelconfig.ColumnModel.Add(new ExcelColumnModel() { Column = isExist.field, ExcelColumn = isExist.value });
                }
            }


            var addPath = FileVariable.TemporaryFilePath + excelconfig.FileName;
            ExcelExportHelper<WelfareDetailListOutput>.Export(exportDataNew, excelconfig, addPath);
            var fileName = _userManager.UserId + "|" + addPath + "|xls";
            var output = new
            {
                name = excelconfig.FileName,
                url = "api/file/Download?encryption=" + DESCEncryption.Encrypt(fileName, "JNPF") //"wwwroot/Template/TemporaryFile/" + excelconfig.FileName //
            };
            return output;




        }
        #endregion

        #region 导出福利使用记录Excel
        /// <summary>
        /// 导出福利使用记录
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("Actions/ExportClear")]
        public async Task<dynamic> ExportClear([FromQuery] WelfareDetailListQueryInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            var exportData = new List<WelfareDetailListOutput>();
            var exportDataNew = new List<WelfareDetailListOutput>();

            var entity = input.Adapt<WelfareBillEntity>();

            if (input.dataType == 0)
            {
                //var data = Clay.Object(await this.GetWelfareUseList(input));
                //exportData = data.Solidify<PageResult<WelfareDetailListOutput>>().list;
                exportData = await this.GetWelfareUseList(input);
            }
            else
            {
                exportData = await this.GetWelfareUseList(input);
            }

            foreach (WelfareDetailListOutput item in exportData)
            {
                exportDataNew.Add(item);
            }

            List<ParamsModel> paramList = "[{\"value\":\"部门\",\"field\":\"departName\"},{\"value\":\"发放总额\",\"field\":\"amount\"},{\"value\":\"使用金额\",\"field\":\"conSumAmt\"},{\"value\":\"剩余金额\",\"field\":\"balance\"},]".ToList<ParamsModel>();
            ExcelConfig excelconfig = new ExcelConfig();
            excelconfig.FileName = userInfo.organizeName + "_" + "福利使用情况" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            excelconfig.HeadFont = "微软雅黑";
            excelconfig.HeadPoint = 10;
            excelconfig.IsAllSizeColumn = true;
            excelconfig.ColumnModel = new List<ExcelColumnModel>();
            List<string> selectKeyList = input.selectKey.Split(',').ToList();
            foreach (var item in selectKeyList)
            {
                var isExist = paramList.Find(p => p.field == item);
                if (isExist != null)
                {
                    excelconfig.ColumnModel.Add(new ExcelColumnModel() { Column = isExist.field, ExcelColumn = isExist.value });
                }
            }
            var addPath = FileVariable.TemporaryFilePath + excelconfig.FileName;
            ExcelExportHelper<WelfareDetailListOutput>.Export(exportDataNew, excelconfig, addPath);
            var fileName = _userManager.UserId + "|" + addPath + "|xls";
            var output = new
            {
                name = excelconfig.FileName,
                //url = "wwwroot/Resources/TemporaryFile/" + excelconfig.FileName
                url = "api/file/Download?encryption=" + DESCEncryption.Encrypt(fileName, "JNPF") //"wwwroot/Template/TemporaryFile/" + excelconfig.FileName //
            };
            return output;
        }
        #endregion
    }
}